package com.lee.sql;

import cn.hutool.core.lang.Singleton;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.core.util.StrUtil;
import com.lee.exception.DataSourceException;
import com.lee.meta.ColumnMeta;
import com.lee.meta.IndexColumnMeta;
import com.lee.meta.IndexMeta;
import com.lee.meta.TableMeta;
import com.lee.type.IndexTypeEnum;

import java.sql.Types;
import java.util.ArrayList;
import java.util.Comparator;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * @description: Oracle SQL
 * @author: 青石路
 * @date: 2024/4/30 15:49
 */
public class OracleSql implements DBSql {



    // 使用 volatile 关键字保证可见性和禁止指令重排序
    private static volatile OracleSql instance;

    // 构造方法私有化，防止外部创建实例
    private OracleSql() {}

    // 提供一个全局访问点，使用双重检查锁定保证线程安全
    public static OracleSql getInstance() {
        if (instance == null) {
            synchronized (Singleton.class) {
                if (instance == null) {
                    instance = new OracleSql();
                }
            }
        }
        return instance;
    }

    @Override
    public String getTableColumnSql(TableQueryParam queryParam) {
        return StrUtil.format("SELECT * FROM {}.{} WHERE 1=0", queryParam.getSchema(), queryParam.getTableName());
    }

    @Override
    public String getMaxValueSql(TableQueryParam queryParam, String columnName) {
        return StrUtil.format("SELECT MAX({}) FROM {}.{}", columnName, queryParam.getSchema(), queryParam.getTableName());
    }

    @Override
    public String getTableDDLSql(TableQueryParam tableQueryParam) {
        // 查询表DDL，不包括主键外的索引
        return StrUtil.format("SELECT DBMS_METADATA.GET_DDL('TABLE', '{}') FROM DUAL", tableQueryParam.getTableName());
    }

    @Override
    public String getCreateTableSql(TableQueryParam queryParam, TableMeta tableMeta, List<ColumnMeta> columnMetas, IndexMeta primaryKeyMeta,
                                    Map<String, IndexMeta> indexMetaMap) {
        StringBuilder createSql = new StringBuilder(
                "CREATE TABLE " + queryParam.getTargetSchema() + "." + queryParam.getTargetTableName() + " ( ");
        for (ColumnMeta columnMeta : columnMetas) {
            createSql.append(columnMeta.getColumnName()).append(" ").append(getColumnType(columnMeta));
            if (columnMeta.getIfNullable() == 0) {
                createSql.append(" NOT NULL");
            }
            createSql.append(",");
        }
        // 主键处理
        if (ObjectUtil.isNotNull(primaryKeyMeta)) {
            List<IndexColumnMeta> indexColumns = primaryKeyMeta.getIndexColumns();
            indexColumns.sort(Comparator.comparingInt(IndexColumnMeta::getOrdinalPosition));
            createSql.append(" PRIMARY KEY (")
                    .append(indexColumns.stream().map(IndexColumnMeta::getColumnName).collect(Collectors.joining(", ")))
                    .append("),");
        }
        for (IndexMeta indexMeta : indexMetaMap.values()) {
            if (indexMeta.getIndexType() == IndexTypeEnum.NORMAL) {
                continue;
            }
            createSql.append("CONSTRAINT ");
            if (StrUtil.isNotBlank(indexMeta.getIndexName())) {
                createSql.append(indexMeta.getIndexName()).append(" ");
            }
            createSql.append("UNIQUE ");
            List<IndexColumnMeta> indexColumns = indexMeta.getIndexColumns();
            indexColumns.sort(Comparator.comparingInt(IndexColumnMeta::getOrdinalPosition));
            createSql.append("(")
                    .append(indexColumns.stream().map(IndexColumnMeta::getColumnName).collect(Collectors.joining(", ")))
                    .append("),");
        }
        // 删除最后一个逗号
        createSql.deleteCharAt(createSql.length() - 1);
        createSql.append(")");
        return createSql.toString();
    }

    private List<String> generateComment(TableMeta tableMeta, List<ColumnMeta> columnMetas) {
        List<String> sqlList = new ArrayList<>();
        // 表注释
        String remarks = tableMeta.getRemarks();
        String tableName = tableMeta.getTableName();
        if (StrUtil.isNotBlank(remarks)) {
            sqlList.add("COMMENT ON TABLE " + tableName + " IS '" + remarks + "'");

        }
        // 列注释
        for (ColumnMeta columnMeta : columnMetas) {
            String columnMetaRemarks = columnMeta.getRemarks();
            if (StrUtil.isBlank(columnMetaRemarks)) {
                continue;
            }
            sqlList.add("COMMENT ON COLUMN " + tableName + "." + columnMeta.getColumnName() + " IS '"
                    + columnMetaRemarks + "'");
        }

        return sqlList;
    }

    private List<String> generateIndexSql(TableMeta tableMeta, Map<String, IndexMeta> indexMetaMap) {
        List<String> sqlList = new ArrayList<>();
        // 普通索引 CREATE INDEX index_name ON table_name(column_name);
        for (IndexMeta indexMeta : indexMetaMap.values()) {
            if (indexMeta.getIndexType() == IndexTypeEnum.UNIQUE) {
                continue;
            }
            StringBuilder sqlBuilder = new StringBuilder("CREATE INDEX ");
            if (StrUtil.isNotBlank(indexMeta.getIndexName())) {
                sqlBuilder.append(indexMeta.getIndexName()).append(" ");
            }
            List<IndexColumnMeta> indexColumns = indexMeta.getIndexColumns();
            indexColumns.sort(Comparator.comparingInt(IndexColumnMeta::getOrdinalPosition));
            sqlBuilder.append(" ON ").append(tableMeta.getTableName()).append("(")
                    .append(indexColumns.stream().map(IndexColumnMeta::getColumnName).collect(Collectors.joining(", ")))
                    .append(")");
            sqlList.add(sqlBuilder.toString());
        }

        return sqlList;
    }

    /**
     * 获取表 字段类型
     *
     * @param columnMeta 列元数据
     * @return mysql 字段类型
     */
    private String getColumnType(ColumnMeta columnMeta) {
        switch (columnMeta.getColumnType()) {
            // 数值类型
            case Types.TINYINT:
                return "NUMBER(3,0)";
            case Types.SMALLINT:
                return "NUMBER(5,0)";
            case Types.INTEGER:
                return "NUMBER(10,0)";
            case Types.BIGINT:
                return "NUMBER(19,0)";
            case Types.FLOAT:
            case Types.REAL:
            case Types.DOUBLE:
                return "FLOAT(24)";
            case Types.DECIMAL:
            case Types.NUMERIC:
                return "NUMBER(" + columnMeta.getPrecision() + "," + columnMeta.getScale() + ")";
            // 字符与字符串类型
            case Types.CHAR:
            case Types.NCHAR:
                return columnMeta.getPrecision() > 0 ? "CHAR(" + columnMeta.getPrecision() + ")" : "CHAR";
            case Types.VARCHAR:
            case Types.NVARCHAR:
                if (columnMeta.getPrecision() > 4000) {
                    return "NCLOB";
                }
                return columnMeta.getPrecision() > 0 && columnMeta.getPrecision() <= 4000
                        ? "NVARCHAR2(" + columnMeta.getPrecision() + ")" : "NVARCHAR2";
            case Types.LONGVARCHAR:
            case Types.LONGNVARCHAR:
            case Types.CLOB:
            case Types.NCLOB:
                return "NCLOB";
            // 日期和时间类型
            case Types.DATE:
                switch (columnMeta.getColumnTypeName()) {
                    case "YEAR":
                        return "NUMBER(4,0)";
                    default:
                        return "DATE";
                }
            case Types.TIME:
            case Types.TIMESTAMP:
                return "DATE";
            // 二进制类型
            case Types.BIT:
            case Types.BOOLEAN:
            case Types.BINARY:
                return columnMeta.getPrecision() > 0 ? "RAW(" + columnMeta.getPrecision() + ")" : "RAW";
            case Types.VARBINARY:
            case Types.BLOB:
            case Types.LONGVARBINARY:
                return "BLOB";
            case Types.OTHER:
                if (columnMeta.getColumnTypeName().contains("VARCHAR")) {
                    return "NVARCHAR2" + (columnMeta.getPrecision() > 0 ? "(" + columnMeta.getPrecision() + ")" : "");
                } else {
                    throw new DataSourceException("不支持的类型：" + columnMeta.getColumnTypeName());
                }
            default:
                throw new DataSourceException("不支持的类型：" + columnMeta.getColumnTypeName());
        }
    }
}
